﻿using System.Collections.Generic;
using ExamPlat.Models;
using ExamPlat.DAL;
using System.Data;
using System;
using ExamPlat.ViewModels;

namespace ExamPlat.Repositories
{
    public class ExamQuesRepository : IExamQuesRepository
    {
        /// <summary>
        /// 增加考题
        /// </summary>
        /// <param name="model">考题信息</param>
        /// <returns></returns>
        public int AddExamQues(ExamQues model)
        {
            try
            {
                string sql = string.Format("select count(1) from ExamQues where ExamQuesTitle = '{0}'", model.ExamQuesTitle);
                int result = SqlHelper.ExecuteScalar(sql);
                if (result == 0)
                {
                    var status = model.ExamQuesStatus ? 1 : 0;
                    sql = string.Format(@"insert into ExamQues(ExamQuesTitle,ExamQuesTime,ExamQuesStatus,ExamQuesDesc,SingleScore,MultipleScore,JudgeScore) 
                                                                                   values('{0}',{1},{2},'{3}',{4},{5},{6})", model.ExamQuesTitle, model.ExamQuesTime, status, model.ExamQuesDesc, model.SingleScore, model.MultipleScore, model.JudgeScore);
                    return SqlHelper.ExecuteTransaction(sql);
                }
                return -1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 删除考题
        /// </summary>
        /// <param name="id">考题ID</param>
        /// <returns></returns>
        public int DelExamQues(int id)
        {
            try
            {
                string sql = string.Format("delete from ExamQuesItemOption where ExamQuesItemID in(select ID from ExamQuesItem where ExamQuesID ={0})", id);
                sql += string.Format(";delete from ExamQuesItem where ExamQuesID = {0}", id);
                sql += string.Format(";delete from ExamQues where ID = {0}", id);
                return SqlHelper.ExecuteTransaction(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据条件查询考题信息
        /// </summary>
        /// <param name="examQuesTitle">考题标题</param>
        /// <returns></returns>
        public IList<ExamQues> GetExamQuesByCondition(string examQuesTitle)
        {
            try
            {
                string sql = "select * from ExamQues  where 1=1 ";
                if (!string.IsNullOrWhiteSpace(examQuesTitle))
                {
                    sql += string.Format(" and ExamQuesTitle like '%{0}%'", examQuesTitle);
                }
                DataSet ds = SqlHelper.ExecuteQuery(sql);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    IList<ExamQues> result = new List<ExamQues>();
                    DataTable dt = ds.Tables[0];
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        ExamQues tmpResult = new ExamQues();
                        tmpResult.ID = (int)dt.Rows[i]["ID"];
                        tmpResult.ExamQuesTitle = dt.Rows[i]["ExamQuesTitle"].ToString();
                        tmpResult.ExamQuesTime = (int)dt.Rows[i]["ExamQuesTime"];
                        tmpResult.ExamQuesStatus = (bool)dt.Rows[i]["ExamQuesStatus"];
                        tmpResult.ExamQuesDesc = dt.Rows[i]["ExamQuesDesc"].ToString();
                        tmpResult.SingleScore = (int)dt.Rows[i]["SingleScore"];
                        tmpResult.MultipleScore = (int)dt.Rows[i]["MultipleScore"];
                        tmpResult.JudgeScore = (int)dt.Rows[i]["JudgeScore"];
                        result.Add(tmpResult);
                    }
                    return result;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据考题ID查询考题信息
        /// </summary>
        /// <param name="id">考题ID</param>
        /// <returns></returns>
        public ExamQues GetExamQuesByID(int id)
        {
            try
            {
                string sql = string.Format("select * from ExamQues  where ID = {0} ", id);
                DataSet ds = SqlHelper.ExecuteQuery(sql);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    ExamQues result = new ExamQues();
                    DataTable dt = ds.Tables[0];
                    result.ID = (int)dt.Rows[0]["ID"];
                    result.ExamQuesTitle = dt.Rows[0]["ExamQuesTitle"].ToString();
                    result.ExamQuesTime = (int)dt.Rows[0]["ExamQuesTime"];
                    result.ExamQuesStatus = (bool)dt.Rows[0]["ExamQuesStatus"];
                    result.ExamQuesDesc = dt.Rows[0]["ExamQuesDesc"].ToString();
                    result.SingleScore = (int)dt.Rows[0]["SingleScore"];
                    result.MultipleScore = (int)dt.Rows[0]["MultipleScore"];
                    result.JudgeScore = (int)dt.Rows[0]["JudgeScore"];
                    return result;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 预览考题信息
        /// </summary>
        /// <param name="id">考题ID</param>
        /// <returns></returns>
        public ViewExamQues PreviewExamQues(int id)
        {
            try
            {
                string sql = string.Format(@"select *,(select count(1) from ExamQuesItem where ExamQuesID={0} and ExamQuesItemType = 1) as SingleCount,
                                                                           (select count(1) from ExamQuesItem where ExamQuesID={0} and ExamQuesItemType = 2) as MultipleCount,
                                                                           (select count(1) from ExamQuesItem where ExamQuesID={0} and ExamQuesItemType = 3) as JudgeCount 
                                                                            from ExamQues  where ID = {0} ", id);
                DataSet ds = SqlHelper.ExecuteQuery(sql);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    ViewExamQues result = new ViewExamQues();
                    DataTable dt = ds.Tables[0];
                    result.ID = (int)dt.Rows[0]["ID"];
                    result.ExamQuesTitle = dt.Rows[0]["ExamQuesTitle"].ToString();
                    result.ExamQuesTime = (int)dt.Rows[0]["ExamQuesTime"];
                    result.ExamQuesStatus = (bool)dt.Rows[0]["ExamQuesStatus"];
                    result.ExamQuesDesc = dt.Rows[0]["ExamQuesDesc"].ToString();
                    result.SingleScore = (int)dt.Rows[0]["SingleScore"];
                    result.MultipleScore = (int)dt.Rows[0]["MultipleScore"];
                    result.JudgeScore = (int)dt.Rows[0]["JudgeScore"];
                    result.SingleCount = (int)dt.Rows[0]["SingleCount"];
                    result.MultipleCount = (int)dt.Rows[0]["MultipleCount"];
                    result.JudgeCount = (int)dt.Rows[0]["JudgeCount"];
                    result.TotalScore = result.SingleScore * result.SingleCount + result.MultipleScore * result.MultipleCount + result.JudgeScore * result.JudgeCount;
                    return result;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 修改考题
        /// </summary>
        /// <param name="model">考题信息</param>
        /// <returns></returns>
        public int UpdateExamQues(ExamQues model)
        {
            try
            {
                string sql = string.Format("select count(1) from ExamQues where ExamQuesTitle = '{0}' and ID != {1}", model.ExamQuesTitle, model.ID);
                int result = SqlHelper.ExecuteScalar(sql);
                if (result == 0)
                {
                    var status = model.ExamQuesStatus ? 1 : 0;
                    sql = string.Format(@"update ExamQues set ExamQuesTitle='{0}',ExamQuesTime={1},ExamQuesStatus={2},ExamQuesDesc='{3}',
                                                              SingleScore={4},MultipleScore={5},JudgeScore={6} where ID = {7}", model.ExamQuesTitle, model.ExamQuesTime,
                                                               status, model.ExamQuesDesc, model.SingleScore, model.MultipleScore, model.JudgeScore, model.ID);
                    sql += string.Format(";update ExamQuesItem set ExamQuesItemScore={0} where ExamQuesID={1} and ExamQuesItemType = 1", model.SingleScore, model.ID);
                    sql += string.Format(";update ExamQuesItem set ExamQuesItemScore={0} where ExamQuesID={1} and ExamQuesItemType = 2", model.MultipleScore, model.ID);
                    sql += string.Format(";update ExamQuesItem set ExamQuesItemScore={0} where ExamQuesID={1} and ExamQuesItemType = 3", model.JudgeScore, model.ID);
                    return SqlHelper.ExecuteTransaction(sql);
                }
                return -1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}